package com.xiaotu.dao.pgsql;

import com.xiaotu.entity.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author zhangxiao
 * @create 2021-10-27 11:33
 */
public interface PgsqlXtIndexDataSourceMapper {

    @Select("<script>" +
            " SELECT " +
            " t3.starid, " +
            " t3.NAME starname, " +
            " t2.tvid, " +
            " t2.tvname, " +
            " CASE  " +
            " WHEN t2.firstplaytime ~* '--' THEN REGEXP_REPLACE(t2.firstplaytime,'--', '-00-00')  " +
            " WHEN t2.firstplaytime ~* '-$' THEN REGEXP_REPLACE(t2.firstplaytime,'-$', '-00')  " +
            " WHEN t2.firstplaytime ~* '^-' THEN REGEXP_REPLACE(t2.firstplaytime,'^-', '0000-')  " +
            " WHEN t2.firstplaytime ~* '^--' THEN REGEXP_REPLACE(t2.firstplaytime,'^--', '0000-00-')  " +
            " ELSE t2.firstplaytime " +
            " END AS firstplaytime, " +
            " t2.releasetime, " +
            " t4.startdate, " +
            " t4.starttime, " +
            " t4.endtime, " +
            " t4.rate, " +
            " t4.market, " +
            " t4.channel_name " +
            " FROM ${tableName} t1 " +
            " INNER JOIN basicinfo.tab_tv_info_to_tencent t2 ON t1.tvid = t2.tvid " +
            " LEFT JOIN basicinfo.tab_star_info_to_tencent t3 ON t1.starid = t3.starid " +
            " LEFT JOIN ( SELECT " +
            " T32.tvid, " +
            " iname, " +
            " tcma.channel_name, " +
            " istartdate startdate, " +
            " ienddate enddate, " +
            " istarttime starttime, " +
            " istoptime endtime, " +
            " ROUND(avg_c_0_rate, 3) rate, " +
            " ROUND(avg_c_0_market, 3) market " +
            "FROM " +
            " stat.tab_city_tvprogram_rounds_week T32 " +
            " LEFT JOIN analysis.tab_channel_map_all tcma ON T32.ichannelid = tcma.channel_id " +
            " LEFT JOIN basicinfo.tab_tv_info_to_tencent tv ON T32.tvid = tv.tvid " +
            " AND tvtype = 1  " +
            "WHERE " +
            " 1 = 1  " +
            " AND T32.iareaid = 19903  " +
            " AND T32.ichannellevel IN ( 1, 2 )  " +
            "AND " +
            " CASE " +
            " <![CDATA[  " +
            "  WHEN to_timestamp ( istoptime, 'HH24MISS' ) < to_timestamp ( '260000', 'HH24MISS' ) THEN " +
            "  to_timestamp ( istoptime, 'HH24MISS' ) ELSE to_timestamp ( '260000', 'HH24MISS' )  " +
            " END - " +
            " CASE " +
            "  WHEN to_timestamp ( istarttime, 'HH24MISS' ) > to_timestamp ( '020000', 'HH24MISS' ) THEN " +
            "  to_timestamp ( istarttime, 'HH24MISS' ) ELSE to_timestamp ( '020000', 'HH24MISS' )  " +
            " END > ( to_timestamp ( istoptime, 'HH24MISS' ) - to_timestamp ( istarttime, 'HH24MISS' ) ) * 0.6  " +
            " ]]>  " +
            " AND T32.avg_c_0_rate IS NOT NULL " +
            " ) t4 ON t1.tvid = t4.tvid  " +
            "WHERE " +
            " t2.tvtype = 1 " +
            "ORDER BY " +
            " t1.tvid, " +
            " t1.starid " +
            "</script>")
    List<XtIndexSyncTvModel> getXtIndexSyncTv(@Param("tableName") String tableName);

    @Select("<script>" +
            " SELECT " +
            " t3.starid, " +
            " t3.NAME starname, " +
            " t2.tvid, " +
            " t2.tvname, " +
            " CASE  " +
            " WHEN t2.firstplaytime ~* '--' THEN REGEXP_REPLACE(t2.firstplaytime,'--', '-00-00')  " +
            " WHEN t2.firstplaytime ~* '-$' THEN REGEXP_REPLACE(t2.firstplaytime,'-$', '-00')  " +
            " WHEN t2.firstplaytime ~* '^-' THEN REGEXP_REPLACE(t2.firstplaytime,'^-', '0000-')  " +
            " WHEN t2.firstplaytime ~* '^--' THEN REGEXP_REPLACE(t2.firstplaytime,'^--', '0000-00-')  " +
            " ELSE t2.firstplaytime " +
            " END AS firstplaytime, " +
            " t2.releasetime, " +
            " t4.totalboxoffice  " +
            " FROM ${tableName} t1 " +
            " INNER JOIN basicinfo.tab_tv_info_to_tencent t2 ON t1.tvid = t2.tvid " +
            " LEFT JOIN basicinfo.tab_star_info_to_tencent t3 ON t1.starid = t3.starid " +
            " LEFT JOIN ( SELECT tvid, MAX ( totalboxoffice ) totalboxoffice FROM basicinfo.tab_movie_record WHERE tvid IS NOT NULL GROUP BY tvid ) t4 ON t1.tvid = t4.tvid  " +
            "WHERE " +
            " t2.tvtype = 2  " +
            "ORDER BY " +
            " t1.tvid, " +
            " t1.starid " +
            "</script>")
    List<XtIndexSyncMovieModel> getXtIndexSyncMovie(@Param("tableName") String tableName);

    @Select("<script>" +
            " SELECT " +
            " t3.starid, " +
            " t3.NAME starname, " +
            " t2.tvid, " +
            " t2.tvname, " +
            " CASE  " +
            " WHEN t2.firstplaytime ~* '--' THEN REGEXP_REPLACE(t2.firstplaytime,'--', '-00-00')  " +
            " WHEN t2.firstplaytime ~* '-$' THEN REGEXP_REPLACE(t2.firstplaytime,'-$', '-00')  " +
            " WHEN t2.firstplaytime ~* '^-' THEN REGEXP_REPLACE(t2.firstplaytime,'^-', '0000-')  " +
            " WHEN t2.firstplaytime ~* '^--' THEN REGEXP_REPLACE(t2.firstplaytime,'^--', '0000-00-')  " +
            " ELSE t2.firstplaytime " +
            " END AS firstplaytime, " +
            " t2.releasetime, " +
            " t5.tencent, " +
            " t5.souhu, " +
            " t5.mgtv, " +
            " t5.iqy, " +
            " t5.youku, " +
            " t5.db_score  " +
            " FROM ${tableName} t1 " +
            " INNER JOIN basicinfo.tab_tv_info_to_tencent t2 ON t1.tvid = t2.tvid " +
            " LEFT JOIN basicinfo.tab_star_info_to_tencent t3 ON t1.starid = t3.starid " +
            " LEFT JOIN ( " +
            " SELECT " +
            "  tv_id, " +
            "  MAX ( CASE WHEN platform = '腾讯视频' THEN play_count :: BIGINT ELSE 0 END ) AS tencent, " +
            "  MAX ( CASE WHEN platform = '搜狐' THEN play_count :: BIGINT ELSE 0 END ) AS souhu, " +
            "  MAX ( CASE WHEN platform = '芒果TV' THEN play_count :: BIGINT ELSE 0 END ) AS mgTV, " +
            "  MAX ( CASE WHEN platform = '爱奇艺' THEN heat :: BIGINT ELSE 0 END ) AS iqy, " +
            "  MAX ( CASE WHEN platform = '优酷' THEN heat :: BIGINT ELSE 0 END ) AS youku, " +
            "  MAX ( db_score ) AS db_score, " +
            "  to_char( MAX ( collect_time ), 'YYYY-MM-DD HH24:MI:SS' ) AS update_time  " +
            " FROM " +
            "  basicinfo.tab_tv_process_data  " +
            " WHERE " +
            "  1 = 1  " +
            "  AND collect_time :: DATE BETWEEN (NOW() - INTERVAL '1 day')::DATE  " +
            "  AND (NOW() - INTERVAL '1 day')::DATE  " +
            " GROUP BY " +
            "  tv_id, " +
            "  collect_time :: DATE  " +
            " ) t5 ON t1.tvid = t5.tv_id :: TEXT  " +
            "WHERE t2.tvtype IN (1,3) " +
            "ORDER BY " +
            " t1.tvid, " +
            " t1.starid " +
            "</script>")
    List<XtIndexSyncNetModel> getXtIndexSyncNetTV(@Param("tableName") String tableName);

    @Select("<script>" +
            " SELECT " +
            " t3.starid, " +
            " t3.NAME starname, " +
            " t2.tvid, " +
            " t2.tvname, " +
            " CASE  " +
            " WHEN t2.firstplaytime ~* '--' THEN REGEXP_REPLACE(t2.firstplaytime,'--', '-00-00')  " +
            " WHEN t2.firstplaytime ~* '-$' THEN REGEXP_REPLACE(t2.firstplaytime,'-$', '-00')  " +
            " WHEN t2.firstplaytime ~* '^-' THEN REGEXP_REPLACE(t2.firstplaytime,'^-', '0000-')  " +
            " WHEN t2.firstplaytime ~* '^--' THEN REGEXP_REPLACE(t2.firstplaytime,'^--', '0000-00-')  " +
            " ELSE t2.firstplaytime " +
            " END AS firstplaytime, " +
            " t2.releasetime, " +
            " t5.tencent, " +
            " t5.souhu, " +
            " t5.mgtv, " +
            " t5.iqy, " +
            " t5.youku, " +
            " t5.db_score  " +
            " FROM ${tableName} t1 " +
            " INNER JOIN basicinfo.tab_tv_info_to_tencent t2 ON t1.tvid = t2.tvid " +
            " LEFT JOIN basicinfo.tab_star_info_to_tencent t3 ON t1.starid = t3.starid " +
            " LEFT JOIN ( " +
            " SELECT " +
            "  tv_id, " +
            "  MAX ( CASE WHEN platform = '腾讯视频' THEN play_count :: BIGINT ELSE 0 END ) AS tencent, " +
            "  MAX ( CASE WHEN platform = '搜狐' THEN play_count :: BIGINT ELSE 0 END ) AS souhu, " +
            "  MAX ( CASE WHEN platform = '芒果TV' THEN play_count :: BIGINT ELSE 0 END ) AS mgTV, " +
            "  MAX ( CASE WHEN platform = '爱奇艺' THEN heat :: BIGINT ELSE 0 END ) AS iqy, " +
            "  MAX ( CASE WHEN platform = '优酷' THEN heat :: BIGINT ELSE 0 END ) AS youku, " +
            "  MAX ( db_score ) AS db_score, " +
            "  to_char( MAX ( collect_time ), 'YYYY-MM-DD HH24:MI:SS' ) AS update_time  " +
            " FROM " +
            "  basicinfo.tab_tv_process_data  " +
            " WHERE " +
            "  1 = 1  " +
            "  AND collect_time :: DATE BETWEEN (NOW() - INTERVAL '1 day')::DATE  " +
            "  AND (NOW() - INTERVAL '1 day')::DATE  " +
            " GROUP BY " +
            "  tv_id, " +
            "  collect_time :: DATE  " +
            " ) t5 ON t1.tvid = t5.tv_id :: TEXT " +
            "WHERE t2.tvtype = 6  " +
            "ORDER BY " +
            " t1.tvid, " +
            " t1.starid  " +
            "</script>")
    List<XtIndexSyncNetModel> getXtIndexSyncNetMovie(@Param("tableName") String tableName);

    @Select("<script>" +
            " SELECT " +
            " t4.starid, " +
            " t4.name starname, " +
            " t3.tvid, " +
            " t3.tvname, " +
            " t1.db_score, " +
            " CASE  " +
            " WHEN t3.firstplaytime ~* '--' THEN REGEXP_REPLACE(t3.firstplaytime,'--', '-00-00')  " +
            " WHEN t3.firstplaytime ~* '-$' THEN REGEXP_REPLACE(t3.firstplaytime,'-$', '-00')  " +
            " WHEN t3.firstplaytime ~* '^-' THEN REGEXP_REPLACE(t3.firstplaytime,'^-', '0000-')  " +
            " WHEN t3.firstplaytime ~* '^--' THEN REGEXP_REPLACE(t3.firstplaytime,'^--', '0000-00-')  " +
            " ELSE t3.firstplaytime " +
            "END AS firstplaytime " +
            "FROM " +
            " basicinfo.tab_tv_process_data t1 " +
            " INNER JOIN ${tableName} t2 ON t1.tv_id :: TEXT = t2.tvid " +
            " LEFT JOIN basicinfo.tab_tv_info_to_tencent t3 ON t1.tv_id :: TEXT = t3.tvid " +
            " LEFT JOIN basicinfo.tab_star_info_to_tencent t4 ON t2.starid = t4.starid  " +
            "WHERE " +
            " t1.platform = '豆瓣'  " +
            " AND t1.collect_time :: DATE = (NOW() - INTERVAL '1 day')::DATE  " +
            "ORDER BY " +
            " t2.tvid, " +
            " t2.starid " +
            "</script>")
    List<XtIndexSyncDouBanModel> getXtIndexSyncDouBan(@Param("tableName") String tableName);

    @Select("<script>" +
            " SELECT " +
            " sw.starid, " +
            " MAX ( starname ) starName, " +
            " COUNT ( DISTINCT sw.tvid ) zpnum  " +
            "FROM " +
            " ${tableName} sw " +
            " INNER JOIN tab_tv_info_to_tencent tv ON sw.tvid = tv.tvid  " +
            "WHERE " +
            " tv.tvtype = 2  " +
            "GROUP BY " +
            " sw.starid " +
            "</script>")
    List<XtIndexSyncSwMovieWorksModel> getXtIndexSyncSwMovieWorks(@Param("tableName") String tableName);

    @Select("<script>" +
            " SELECT " +
            " sw.starid, " +
            " MAX ( starname ) starName, " +
            " COUNT ( DISTINCT sw.tvid ) zpnum  " +
            "FROM " +
            " ${tableName} sw " +
            " INNER JOIN tab_tv_info_to_tencent tv ON sw.tvid = tv.tvid  " +
            "WHERE " +
            " tv.tvtype = 6  " +
            "GROUP BY " +
            " sw.starid " +
            "</script>")
    List<XtIndexSyncSwNetMovieWorksModel> getXtIndexSyncSwNetMovieWorks(@Param("tableName") String tableName);
}
